﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WMSSyncData.Utils;

namespace SyncPartNumber
{
    class MyBll
    {
        public string SN = "105";

        public int UpdatePartNumber()
        {
            int ret = 0;
            //StringBuilder sqlCmd = new StringBuilder();

            var sqlCnt = "select count(0) from ttcibd001" + SN + "wmsview";
            var dtOracleCnt = OracleDBHelper.GetDataSet(sqlCnt).Tables[0];
            if (dtOracleCnt.Rows.Count == 0)
            {
                return 0;
            }
            int cnt = Convert.ToInt32(dtOracleCnt.Rows[0][0]);

            var sqlOracle = @"select * from (
                            select rownum as RN,ITEM as MaterialCode,Unit,ITEM as MaterialName,effdays as ShelfLifeTime,
                                    COMPANY as Type,PROD_TYPE as Spec,item_cls as ERPType,LAST_MODI_DATE as UpdateTime
                            from ttcibd001" + SN + "wmsview where LAST_MODI_DATE>SYSDATE-3)";
            var dtOracle = OracleDBHelper.GetDataSet(sqlOracle).Tables[0];

            if (dtOracle.Rows.Count == 0)
            {
                return 0;
            }

            for (int i = 0; i < dtOracle.Rows.Count; i++)
            {
                var sql = "Select * from MdcdatMaterial where MaterialCode='{0}';";
                var MaterialCode = dtOracle.Rows[i]["MaterialCode"].ToString();
                var UpdateTime = dtOracle.Rows[i]["UpdateTime"];
                sql = string.Format(sql, MaterialCode);
                var hasData = SqlDBHelper.GetDataSet(sql).Tables[0];
                string sqlUpd = "";
                if (hasData.Rows.Count > 0)
                {
                    if (UpdateTime.Equals(hasData.Rows[0]["UpdateTime"]))
                        continue;
                    sqlUpd = @"update MdcdatMaterial
                                set 
                                MaterialName='{1}',
                                ShelfLifeTime={2},
                                Type='{3}',
                                Spec='{4}',
                                ERPType='{5}',
                                UpdateTime='{6}',
                                Unit='{7}'
                                where MaterialCode = '{0}';";
                    sqlUpd = string.Format(sqlUpd, dtOracle.Rows[i]["MaterialCode"].ToString(),
                                                dtOracle.Rows[i]["MaterialName"].ToString(),
                                                dtOracle.Rows[i]["ShelfLifeTime"],
                                                dtOracle.Rows[i]["Type"].ToString(),
                                                dtOracle.Rows[i]["Spec"].ToString(),
                                                dtOracle.Rows[i]["ERPType"].ToString(),
                                                dtOracle.Rows[i]["UpdateTime"].ToString(),
                                                dtOracle.Rows[i]["Unit"].ToString());
                    ret++;
                    //sqlCmd.Append(sqlUpd);
                }
                else
                {
                    sqlUpd = @"insert MdcdatMaterial(MaterialCode,MaterialName,ShelfLifeTime,Type,Spec,ERPType,UpdateTime,Unit)
                                values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}');";
                    sqlUpd = string.Format(sqlUpd, dtOracle.Rows[i]["MaterialCode"].ToString(),
                                                dtOracle.Rows[i]["MaterialName"].ToString(),
                                                dtOracle.Rows[i]["ShelfLifeTime"],
                                                dtOracle.Rows[i]["Type"].ToString(),
                                                dtOracle.Rows[i]["Spec"].ToString(),
                                                dtOracle.Rows[i]["ERPType"].ToString(),
                                                dtOracle.Rows[i]["UpdateTime"].ToString(),
                                                dtOracle.Rows[i]["Unit"].ToString());
                    ret++;
                    //sqlCmd.Append(sqlUpd);
                }
                SqlDBHelper.ExcuteSQL(sqlUpd);
            }

            //if (string.IsNullOrEmpty(sqlCmd.ToString()) == false)
            //{
            //    SqlDBHelper.ExcuteSQL(sqlCmd.ToString());
            //    return ret;
            //}

            return ret;
        }

        public int UpdatePartNumber(int startRowNum, int endRowNum)
        {
            int ret = 0;
            StringBuilder sqlCmd = new StringBuilder();

            var sqlCnt = "select count(0) from ttcibd001" + SN + "wmsview";
            var dtOracleCnt = OracleDBHelper.GetDataSet(sqlCnt).Tables[0];
            if (dtOracleCnt.Rows.Count == 0)
            {
                return 0;
            }
            int cnt = Convert.ToInt32(dtOracleCnt.Rows[0][0]);

            var sqlOracle = @"select * from (
                            select rownum as RN,ITEM as MaterialCode,ITEM as MaterialName,effdays as ShelfLifeTime,
                                    COMPANY as Type,PROD_TYPE as Spec,item_cls as ERPType,LAST_MODI_DATE as UpdateTime
                            from ttcibd001101wmsview where rownum  <= {0}) where RN >= {1} and MaterialCode like '%HW6860-15-000-R%'";
            sqlOracle = string.Format(sqlOracle, endRowNum, startRowNum);
            var dtOracle = OracleDBHelper.GetDataSet(sqlOracle).Tables[0];

            if (dtOracle.Rows.Count == 0)
            {
                return 0;
            }

            for (int i = 0; i < dtOracle.Rows.Count; i++)
            {
                var sql = "Select * from MdcdatMaterial where MaterialCode='{0}';";
                var MaterialCode = dtOracle.Rows[i]["MaterialCode"].ToString();
                var UpdateTime = dtOracle.Rows[i]["UpdateTime"];
                sql = string.Format(sql, MaterialCode);
                var hasData = SqlDBHelper.GetDataSet(sql).Tables[0];
                string sqlUpd = "";
                if (hasData.Rows.Count > 0)
                {
                    if (UpdateTime.Equals(hasData.Rows[0]["UpdateTime"]))
                        continue;
                    sqlUpd = @"update MdcdatMaterial
                                set 
                                MaterialName='{1}',
                                ShelfLifeTime={2},
                                Type='{3}',
                                Spec='{4}',
                                ERPType='{5}',
                                UpdateTime='{6}'
                                where MaterialCode = '{0}';";
                    sqlUpd = string.Format(sqlUpd, dtOracle.Rows[i]["MaterialCode"].ToString(),
                                                dtOracle.Rows[i]["MaterialName"].ToString(),
                                                dtOracle.Rows[i]["ShelfLifeTime"],
                                                dtOracle.Rows[i]["Type"].ToString(),
                                                dtOracle.Rows[i]["Spec"].ToString(),
                                                dtOracle.Rows[i]["ERPType"].ToString(),
                                                dtOracle.Rows[i]["UpdateTime"].ToString());
                    ret++;
                    sqlCmd.Append(sqlUpd);
                }
                else
                {
                    sqlUpd = @"insert MdcdatMaterial(MaterialCode,MaterialName,ShelfLifeTime,Type,Spec,ERPType,UpdateTime)
                                values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}');";
                    sqlUpd = string.Format(sqlUpd, dtOracle.Rows[i]["MaterialCode"].ToString(),
                                                dtOracle.Rows[i]["MaterialName"].ToString(),
                                                dtOracle.Rows[i]["ShelfLifeTime"],
                                                dtOracle.Rows[i]["Type"].ToString(),
                                                dtOracle.Rows[i]["Spec"].ToString(),
                                                dtOracle.Rows[i]["ERPType"].ToString(),
                                                dtOracle.Rows[i]["UpdateTime"].ToString());
                    ret++;
                    sqlCmd.Append(sqlUpd);
                }
            }

            if (string.IsNullOrEmpty(sqlCmd.ToString()) == false)
            {
                SqlDBHelper.ExcuteSQL(sqlCmd.ToString());
                return ret;
            }

            return 0;
        }
    }
}
